
	IF NOT EXISTS( SELECT * FROM sys.schemas WHERE name = 'platform' )
	BEGIN
		EXECUTE('CREATE SCHEMA [platform]')
	END
		-- GRANT ...
		-- DENY ...
	GO


	--********************************************************
	--	Tabela com o nome dos objetos
	--********************************************************
	IF OBJECT_ID('[platform].objectQualifiedNames', 'U') IS NOT NULL
		DROP TABLE [platform].objectQualifiedNames
	GO

	CREATE TABLE [platform].objectQualifiedNames
	(
		LoweredQualifiedName	VARCHAR(255)			NOT NULL,
		QualifiedNameID			BIGINT IDENTITY(1,1)	NOT NULL,

		CONSTRAINT PK_objectQualifiedNames PRIMARY KEY (LoweredQualifiedName)
	)

	-- Type Sizes
	-- FieldNames	= VARCHAR(256)
	-- Token		= VARCHAR(16)

	--********************************************************
	--	Tabela de valores				
	--********************************************************
	IF OBJECT_ID('[platform].fieldValues', 'U') IS NOT NULL
		DROP TABLE [platform].fieldValues
	GO

	CREATE TABLE [platform].fieldValues
	(
		LoweredFieldName	VARCHAR(200)	NOT NULL, 
		ReversedTokenId		VARCHAR(64)		NOT NULL, 
		Value				VARBINARY(512)	NULL,	-- Valor utilizado para pesquisa
		TextValue			NVARCHAR(4000)	NULL, 
		OriginalFieldName	VARCHAR(256)	NOT NULL,
		CreatedOn			DATETIME		NOT NULL,
		UpdatedOn			DATETIME		NOT NULL,
		CONSTRAINT PK_fieldValues PRIMARY KEY (LoweredFieldName, ReversedTokenId)
	)
	GO

	-- índice criado para realizar as consultas por campo e valores 
	-- [OBSOLETE] Não há necessidade para criar esse índice, pois o índice abaixo () já é o suficiente
	CREATE INDEX IX_FieldValues_Indexes
		ON [platform].fieldValues ( LoweredFieldName, Value, ReversedTokenID ) 
	GO

	-- índica criado para buscar somente pelos valores dos campos
	CREATE INDEX IX_FieldValues_Value
		ON [platform].fieldValues ( Value, LoweredFieldName, ReversedTokenID )
	GO

	--********************************************************
	--	Tabela de relacionamento entre as entidades
	--********************************************************
	IF OBJECT_ID('[platform].dataObjectRelationships', 'U') IS NOT NULL
		DROP TABLE [platform].dataObjectRelationships
	GO

	CREATE TABLE [platform].dataObjectRelationships
	(
		ParentLoweredFieldName	VARCHAR(200)	NOT NULL,	-- Nome do campo (FIELD) que contém o relacionamento
		ParentReversedTokenId	VARCHAR(64)		NOT NULL,	-- ID da entidade (documento) que possui a outra entidade (documento)
		[Order]					INT				NOT NULL,	-- Ordem de apresentação do documento no relacionamento
		ChildLoweredName		VARCHAR(256)	NOT NULL,	-- Nome da entidade (SCHEMA)
		ChildReversedTokenId	VARCHAR(64)		NOT NULL,	-- ID da entidade
		ParentOriginalFieldName	VARCHAR(256)	NOT NULL,	-- Nome completo do campo
		CreatedOn				DATETIME		NOT NULL,
		UpdatedOn				DATETIME		NOT NULL,
		CONSTRAINT PK_dataObjectRelationships PRIMARY KEY (ParentLoweredFieldName, ParentReversedTokenId, [Order])
	)
	GO

	-- Indice criado para facilitar as buscas pelas dependências
	CREATE INDEX IX_dataObjectRelationships 
		ON [platform].dataObjectRelationships(ChildLoweredName, ChildReversedTokenId)
	GO

	-- Drop stored procedure if it already exists
	IF EXISTS (
	  SELECT * 
		FROM INFORMATION_SCHEMA.ROUTINES 
	   WHERE SPECIFIC_SCHEMA = N'platform'
		 AND SPECIFIC_NAME = N'pInsertAndUpdateFieldValues' 
	)
	   DROP PROCEDURE [platform].pInsertAndUpdateFieldValues
	GO

	CREATE PROCEDURE [platform].pInsertAndUpdateFieldValues
		@FieldAndValues		XML 
	AS
		SET NOCOUNT ON;
	
		--------------------------
		-- Atualização dos campos
		MERGE INTO [platform].fieldValues original
		USING (SELECT	data.content.query('.') AS FieldXml
						, data.content.value('./@t', 'varchar(64)')					AS ReversedTokenId
						, data.content.value('./@original', 'varchar(256)' )		AS OriginalFieldName
						, LOWER( data.content.value('./@name', 'varchar(200)') )	AS LoweredFieldName
						, data.content.value('./@txt', 'nvarchar(4000)')			AS FieldValue
						, data.content.value('./@bin', 'varbinary(512)')			AS BinValue
				FROM @FieldAndValues.nodes('/doc/fields/f') AS data(content) ) AS sourceData

			-- Critério para atualizar os campos do documento
			ON (original.LoweredFieldName = sourceData.LoweredFieldName
				AND original.ReversedTokenId = sourceData.ReversedTokenId)
				
			-- Atualiza somente se houve mudança
		WHEN MATCHED AND original.Value <> sourceData.BinValue THEN
			UPDATE SET Value = sourceData.BinValue, TextValue = sourceData.FieldValue, UpdatedOn = GETDATE()

			-- Adiciona
		WHEN NOT MATCHED THEN
			INSERT (LoweredFieldName, OriginalFieldName, ReversedTokenId, Value, TextValue, CreatedOn, UpdatedOn)
				VALUES (sourceData.LoweredFieldName, sourceData.OriginalFieldName, sourceData.ReversedTokenId, sourceData.BinValue, sourceData.FieldValue, GETDATE(), GETDATE())

			-- Exclui
		WHEN NOT MATCHED BY SOURCE THEN
			DELETE;


		-----------------------------------
		-- Atualização dos relacionamentos
		MERGE INTO [platform].dataObjectRelationships AS original
		USING (SELECT	
						data.relations.query('.')	AS RelationXml
						, data.relations.value( './@p-token', 'varchar(64)' )			AS ParentReversedTokenId
						, LOWER( data.relations.value( './@p-name', 'varchar(200)' ) )	AS ParentLoweredFieldName
						, data.relations.value( './@p-orig', 'varchar(256)' )			AS ParentOriginalFieldName
						, data.relations.value( './@order', 'int' )						AS RelationOrder
						, LOWER( data.relations.value( './@c-name', 'varchar(256)' ) )	AS ChildLoweredName
						, data.relations.value( './@c-token', 'varchar(64)' )			AS ChildReversedTokenId
				FROM @FieldAndValues.nodes('/doc/refs/r') AS data(relations) ) AS sourceData
			ON original.ParentLoweredFieldName = sourceData.ParentLoweredFieldName
				AND original.ParentReversedTokenId = sourceData.ParentReversedTokenId
				AND original.[Order] = sourceData.RelationOrder

			-- Atualização do relationamento
		WHEN MATCHED THEN
			UPDATE SET 
				ChildLoweredName = sourceData.ChildLoweredName
				, ChildReversedTokenId = sourceData.ChildReversedTokenId
				, UpdatedOn = GETDATE()
		
			-- Adiciona o relacionamento
		WHEN NOT MATCHED THEN
			INSERT ( ParentLoweredFieldName, ParentReversedTokenId, ParentOriginalFieldName, [Order], ChildLoweredName, ChildReversedTokenId, CreatedOn, UpdatedOn )
				VALUES ( sourceData.ParentLoweredFieldName, sourceData.ParentReversedTokenId, sourceData.ParentOriginalFieldName, sourceData.RelationOrder, sourceData.ChildLoweredName, sourceData.ChildReversedTokenId, GETDATE(), GETDATE() )

			-- Não precisa mais do relacionamento
		WHEN NOT MATCHED BY SOURCE THEN
			DELETE;
	GO


	-- =============================================
	-- SP de seleção dos dados dos documentos filhos
	-- relacionados a um documento pai
	-- =============================================

	-- Drop stored procedure if it already exists
	IF EXISTS (
	  SELECT * 
		FROM INFORMATION_SCHEMA.ROUTINES 
	   WHERE SPECIFIC_SCHEMA = N'platform'
		 AND SPECIFIC_NAME = N'pSelectDocumentChildren' 
	)
	   DROP PROCEDURE [platform].pSelectDocumentChildren
	GO

	CREATE PROCEDURE [platform].pSelectDocumentChildren
		@LoweredSchemaName	VARCHAR(200), 
		@ReversedTokenId	VARCHAR(64)
	AS
	
		SELECT	children.ParentLoweredFieldName,
				fields.* 
		FROM [platform].fieldValues fields
			INNER JOIN [platform].dataObjectRelationships children 
				ON children.ChildLoweredName LIKE fields.LoweredFieldName + '%'
					AND children.ChildReversedTokenId = fields.ReversedTokenId
		WHERE children.ParentLoweredFieldName LIKE @LoweredSchemaName + '%'
		  AND children.ParentReversedTokenId = @ReversedTokenId	
		ORDER BY children.ParentLoweredFieldName, children.ParentReversedTokenId, children.[Order]
	GO